![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Registering ApplicationsWhen you register an application, the name and the actions performed by that application are stored in the database to assist with debugging and performance tuning efforts. When an application is registered, its name and actions are recorded in the V$SESSION and V$SQLAREA views. This information can be used later to track problems. To register an application, use the following procedures, available in the RDBMS_APPLICATION_INFO package:
By registering the application, you can track many different parameters. Some of the values available through V$SQLAREA are given here:
These parameters can provide valuable information when you are trying to debug various modules within your application. The information is enhanced by the addition of actions, which can further identify sections of your application. SummaryDetermining whether your SQL statements are properly optimized can be as important as anything else you can do to tune your system. An improperly tuned SQL statement can nullify any work you have done to optimize the database system. A well-tuned server system that is handling hundreds or thousands of unnecessary SQL statements can be perceived to have poor performance when, in reality, there is just an abundance of excess work being done. The Oracle SQL Trace facility and the EXPLAIN PLAN command can be valuable tools in debugging inefficient SQL code. The SQL Trace facility and its companion program TKPROF can give valuable information into such areas as these:
The EXPLAIN PLAN command is used to display the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. By analyzing the execution plan the Oracle optimizer has chosen, and by knowing your data and application, you should be able to determine whether the optimizer has chosen the correct execution plan for your application. If you do not agree with the execution plan that has been chosen, you can change it by modifying your SQL statements or by using hints (as described in Chapter 30, Using Hints). EXPLAIN PLAN can help you rewrite your SQL statements to take better advantage of such things as indexes and hash keys. By analyzing the output, you may be able to provide hints that the Oracle optimizer can use to take better advantage of your knowledge of your data. By using hints, you may be able to take better advantage of features such as the Oracle Parallel Query option. Together, SQL Trace, EXPLAIN PLAN, and application registration can all assist in optimizing your SQL statements and your application. By using these features, you can enhance the performance of your system.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |